package com.suypower.carManage.json;

import com.jarp.db.DAO;
import com.opensymphony.xwork2.ActionContext;
import com.opensymphony.xwork2.ActionSupport;
import com.suypower.carManage.VO.CarVO;
import com.suypower.carManage.VO.DriverVO;
import com.suypower.carManage.comm.ConstUtil;
import com.suypower.carManage.comm.SQLHelp;
import jxl.Workbook;
import jxl.format.UnderlineStyle;
import jxl.write.*;
import net.sf.json.JSONArray;

import javax.servlet.ServletException;
import java.io.*;
import java.util.*;
import org.apache.commons.lang3.StringUtils;

public class DriverJson extends ActionSupport {

    private String id;
    private String name;
    private String sex;
    private String property;
    private String born_form;
    private String born_end;
    private String id_code;
    private String phone_num;
    private String car_model;
    private String sideline;
    private String start = "1";
    private String limit = "20";
    private String _dc;
    InputStream excelStream;
    ActionContext cxt = ActionContext.getContext();
    Map<String, Object> session = cxt.getSession();
    String userid = (String) session.get("userid");
    private String excelTitle;
    private String title = "驾驶员列表";


    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

    public String get_dc() {
        return _dc;
    }

    public void set_dc(String dc) {
        _dc = dc;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public String getProperty() {
        return property;
    }

    public void setProperty(String property) {
        this.property = property;
    }

    public String getBorn_form() {
        return born_form;
    }

    public void setBorn_form(String bornForm) {
        born_form = bornForm;
    }

    public String getBorn_end() {
        return born_end;
    }

    public void setBorn_end(String bornEnd) {
        born_end = bornEnd;
    }

    public String getId_code() {
        return id_code;
    }

    public void setId_code(String idCode) {
        id_code = idCode;
    }

    public String getPhone_num() {
        return phone_num;
    }

    public void setPhone_num(String phoneNum) {
        phone_num = phoneNum;
    }

    public String getCar_model() {
        return car_model;
    }

    public void setCar_model(String carModel) {
        car_model = carModel;
    }

    public String getSideline() {
        return sideline;
    }

    public void setSideline(String sideline) {
        this.sideline = sideline;
    }

    public String getStart() {
        return start;
    }

    public void setStart(String start) {
        this.start = start;
    }

    public String getLimit() {
        return limit;
    }

    public void setLimit(String limit) {
        this.limit = limit;
    }


    public String execute() {
        String res = "";
        DAO dao = new DAO();
        int iStart = Integer.parseInt(start);
        int iPageSize = Integer.parseInt(limit);
        int iPage = iStart / iPageSize + 1;
        ArrayList<DriverVO> list = new ArrayList<DriverVO>();
        String sql = this.getSql();
        try {
            Vector v = dao.getDataSet(ConstUtil.DBNAME, sql, iPage, iPageSize);
//             Vector v = dao.getDataSet(ConstUtil.DBNAME, sql);
            if (v != null) {
                for (int i = 0; i < v.size(); i++) {
                    Hashtable hash = (Hashtable) v.get(i);
                    DriverVO driverVO = new DriverVO();
                    driverVO.setId((String) hash.get("ID"));
                    driverVO.setName((String) hash.get("NAME"));
                    driverVO.setSex((String) hash.get("SEX"));
                    driverVO.setProperty((String) hash.get("PROPERTY"));
                    driverVO.setBorn((String) hash.get("BORN"));
                    driverVO.setId_code((String) hash.get("ID_CODE"));
                    driverVO.setPhone_num((String) hash.get("PHONE_NUM"));
                    driverVO.setCar_model((String) hash.get("CAR_MODEL"));
                    driverVO.setSideline((String) hash.get("SIDELINE"));
                    driverVO.setHead_image((String) hash.get("HEAD_IMAGE"));
                    driverVO.setExperience((String) hash.get("EXPERIENCE"));
                    driverVO.setGlory((String) hash.get("GLORY"));
                    driverVO.setAb((String)hash.get("AB"));
                    driverVO.setWorkage((String)hash.get("WORKAGE"));
                    list.add(driverVO);
                }
                String totalCountSQL = "select count(1) CNT from (" + sql + " ) zz ";
                String totalCount = dao.getDataValue(ConstUtil.DBNAMEODS, totalCountSQL);
                JSONArray json = JSONArray.fromObject(list);
                ActionContext.getContext().put("data", "{totalCount:" + totalCount + ",data:" + json.toString() + "}");
            } else {
                ActionContext.getContext().put("data", "{totalCount:" + 0 + ",data:''}");
            }
        } catch (ServletException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        res = this.SUCCESS;
        return res;
    }

    public String getSql(){
        String sql = "SELECT * FROM tb_driver t WHERE 1=1 ";
        name=SQLHelp.splitStrFromWeb(name);
        if (!StringUtils.isEmpty(name)) {
            sql += SQLHelp.createSqlInWithAlias("name", "t", name);
        }
        if (sex != null && !sex.equals("") && !sex.equals("3")) {
            sql += " AND t.sex LIKE '%" + sex + "%' ";
        }
        if (property != null && !property.equals("")) {
            sql += " AND t.property LIKE '%" + property + "%' ";
        }
        if (born_form != null && !born_form.equals("")) {
            if (born_end != null && !born_end.equals("")) {
                sql += " AND (t.born<=" + born_end + " AND t.born>=" + born_form + ")";
            }
        }
        if (id_code != null && !id_code.equals("")) {
            sql += " AND id_code LIKE '%" + id_code + "%' ";
        }
        if (phone_num != null && !phone_num.equals("")) {
            sql += " AND phone_num LIKE '%" + phone_num + "%' ";
        }
        if (car_model != null && !car_model.equals("")) {
            sql += " AND car_model LIKE '%" + car_model + "%' ";
        }
        if (sideline != null && !sideline.equals("")) {
            sql += " AND sideline LIKE '%" + sideline + "%' ";
        }
        return sql;
    }

    public String getExcel() throws UnsupportedEncodingException, IOException, WriteException {
        System.out.println("导出Excel");
        excelStream = this.getExcelInputStream();
        return "excel";

    }

    public InputStream getExcelInputStream() throws IOException, WriteException {
        ByteArrayOutputStream out = new ByteArrayOutputStream();
        create(out);
        return new ByteArrayInputStream(out.toByteArray());
    }

    public void create(OutputStream out) throws IOException, WriteException {
        DAO dao=new DAO();
        excelTitle = title + ".xls";
        excelTitle = new String(excelTitle.getBytes(), "ISO8859-1");
        List<String> count_name = new ArrayList<String>();
        ;    //用来记录具体的列名
        count_name.add("姓名");
        count_name.add("性别");
        count_name.add("出生年月");
        count_name.add("身份证号码");
        count_name.add("电话号码");
        count_name.add("准驾车型");
        count_name.add("是否兼职");
        count_name.add("工龄");
        WritableWorkbook book = Workbook.createWorkbook(out);
        WritableSheet sheet = book.createSheet(title, 0);
        sheet.setRowView(0, 810);//设置大标题格式
        for (int i = 2; i < 100; i++) {
            sheet.setRowView(i, 320);
        }
        sheet.setColumnView(0, 18);
        sheet.setColumnView(1, 12);//设置第一行的宽度  //注意宽度和高度的单位不相同
        sheet.setColumnView(2, 30);
        sheet.setColumnView(3, 30);
        sheet.setColumnView(4, 30);
        sheet.setColumnView(5, 17);
        sheet.setColumnView(6, 17);
        sheet.setColumnView(7, 17);
        sheet.setColumnView(8, 25);
        sheet.setColumnView(9, 25);
        sheet.setColumnView(10, 35);
        sheet.setColumnView(11, 25);
        sheet.setColumnView(12, 25);
        sheet.setColumnView(13, 25);
        sheet.setColumnView(14, 25);
        sheet.setColumnView(15, 25);
        sheet.setColumnView(16, 25);
        sheet.setColumnView(17, 25);

        WritableFont wfc = new WritableFont(WritableFont.createFont("黑体"), 16, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
        WritableCellFormat wcfFC = new WritableCellFormat(wfc);
        wcfFC.setAlignment(jxl.format.Alignment.CENTRE); // 设置横向对齐方式
        wcfFC.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE); //设置纵向对齐方式
        // wcfFC.setBorder(Border.ALL, BorderLineStyle.THIN);  //设置单元格边框和边框线条样式
        sheet.addCell(new Label(0, 0, title, wcfFC));

        //设置小标题格式
        WritableFont wfc2 = new WritableFont(WritableFont.createFont("黑体"), 11, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
        WritableCellFormat wcfFC2 = new WritableCellFormat(wfc2);
        wcfFC2.setAlignment(jxl.format.Alignment.CENTRE); // 设置横向对齐方式
        wcfFC2.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE); //设置纵向对齐方式
        wcfFC2.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);  //设置单元格边框和边框线条样式
        wcfFC2.setWrap(true);

        //设置普通文字格式
        WritableFont wfc3 = new WritableFont(WritableFont.createFont("宋体"), 10, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
        WritableCellFormat wcfFC3 = new WritableCellFormat(wfc3);
        wcfFC3.setAlignment(jxl.format.Alignment.CENTRE); // 设置横向对齐方式
        wcfFC3.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE); //设置纵向对齐方式
        wcfFC3.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);  //设置单元格边框和边框线条样式
        wcfFC3.setWrap(true);
        sheet.addCell(new Label(0, 0, title, wcfFC));
        sheet.mergeCells(0, 0, count_name.size() - 1, 0);
        for (int i = 0; i < count_name.size(); i++) {
            try {

                sheet.addCell(new Label(i, 1, count_name.get(i).split(",")[0], wcfFC2));
            } catch (Exception ex) {
            }
        }
        ArrayList<CarVO> list = new ArrayList<CarVO>();
        String sql = this.getSql();
        Vector v = null;
        try {
            v = dao.getDataSet(ConstUtil.DBNAME, sql);

        } catch (ServletException e) {
            e.printStackTrace();
        }
        if (v != null) {

            for(int i=1;i<v.size()+2;i++){
                sheet.setRowView(i, 400);
            }
            int hang = 2;     //数据应该写在多少行
            for (int i = 0; i < v.size(); i++) {
                ArrayList<String> rowlist = new ArrayList<String>();
                Hashtable hash = (Hashtable) v.get(i);
                String sex=(String) hash.get("SEX");
                if(sex.equals("1")){
                    sex="男";
                }else if(sex.equals("2")){
                    sex="女";
                }
                String Sideline=(String) hash.get("SIDELINE");
                if(Sideline.equals("yes")){
                    Sideline="是";
                }else if ((Sideline.equals("no"))){
                    Sideline="否";
                }
                DriverVO driverVO = new DriverVO();
                driverVO.setId((String) hash.get("ID"));
                driverVO.setName((String) hash.get("NAME"));
                driverVO.setSex(sex);
                driverVO.setProperty((String) hash.get("PROPERTY"));
                driverVO.setBorn((String) hash.get("BORN"));
                driverVO.setId_code((String) hash.get("ID_CODE"));
                driverVO.setPhone_num((String) hash.get("PHONE_NUM"));
                driverVO.setCar_model((String) hash.get("CAR_MODEL"));
                driverVO.setSideline(Sideline);
                driverVO.setHead_image((String) hash.get("HEAD_IMAGE"));
                driverVO.setExperience((String) hash.get("EXPERIENCE"));
                driverVO.setGlory((String) hash.get("GLORY"));
                driverVO.setWorkage((String)hash.get("WORKAGE"));
                rowlist.add(driverVO.getName());
                rowlist.add(driverVO.getSex());
                rowlist.add(driverVO.getBorn());
                rowlist.add(driverVO.getId_code());
                rowlist.add(driverVO.getPhone_num());
                rowlist.add(driverVO.getCar_model());
                rowlist.add(driverVO.getSideline());
                rowlist.add(driverVO.getWorkage());
//                rowlist.add(driverVO.getExperience());
//                rowlist.add(driverVO.getGlory());
                for (int i2 = 0; i2 < rowlist.size(); i2++) {
                    String thisvalue = rowlist.get(i2);
                    if (thisvalue == null || thisvalue.equals("")) {
                        thisvalue = "";
                    }
                    sheet.addCell(new Label(i2, hang, thisvalue, wcfFC3));
                }
                hang++;
            }
            book.write();
            book.close();
        }
    }


    public InputStream getExcelStream() {
        return excelStream;
    }

    public void setExcelStream(InputStream excelStream) {
        this.excelStream = excelStream;
    }

    public String getExcelTitle() {
        return excelTitle;
    }

    public void setExcelTitle(String excelTitle) {
        this.excelTitle = excelTitle;
    }
}
